package com.ruoyi.lowcode.jsqlparser;

import com.ruoyi.common.utils.StringUtils;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.util.CollectionUtils;

import java.io.StringReader;
import java.util.*;

public class JSqlParserUtils {

    /**
     * 查询sql解析，获取查询字段等信息
     *
     * @param sql
     * @return
     * @throws JSQLParserException
     */
    public static NormalSqlStructure getStructure(String sql) throws JSQLParserException {
        NormalSqlStructure normalSqlStructure = new NormalSqlStructure();

        if (StringUtils.isEmpty(sql)) {
            throw new RuntimeException("未检测到SQL");
        }

        sql = sql.replace("#WHERESQL", "");

        normalSqlStructure.setSql(sql);

        // 解析表名
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        Statement statement = parserManager.parse(new StringReader(sql));
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableNameList = tablesNamesFinder.getTableList(statement);
        normalSqlStructure.setTableNameList(tableNameList);

        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        Map<String, String> tableAlias = new HashMap<>();

        if (ObjectUtils.isNotEmpty(plainSelect.getFromItem().getAlias())) {
            tableAlias.put(plainSelect.getFromItem().getAlias().getName(), ((Table) plainSelect.getFromItem()).getName());
        } else {
            String tableName = ((Table) plainSelect.getFromItem()).getName();
            tableAlias.put(tableName, tableName);
        }

        // 关联字段信息
        Map<String, String> joinFields = new HashMap<>();

        if (!CollectionUtils.isEmpty(plainSelect.getJoins())) {
            for (Join join : plainSelect.getJoins()) {
                if (ObjectUtils.isNotEmpty(join.getRightItem().getAlias())) {
                    getJoinField(join, joinFields, join.getRightItem().getAlias().getName());
                    tableAlias.put(join.getRightItem().getAlias().getName(), ((Table) join.getRightItem()).getName());
                } else {
                    String tableName = ((Table) join.getRightItem()).getName();
                    getJoinField(join, joinFields, tableName);
                    tableAlias.put(tableName, tableName);
                }

            }
        }

        List<SelectItem> selectItems = plainSelect.getSelectItems();

        List<NormalSqlField> fieldList = new ArrayList<>();
        for (SelectItem selectItem : selectItems) {
            NormalSqlField normalSqlField = new NormalSqlField();

            if (selectItem.toString().contains("*")) {
                // 当查询执行为“*”或“.*”的情况
                continue;
            }

            SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
            Column column = (Column) selectExpressionItem.getExpression();
            normalSqlField.setFieldName(column.getColumnName());
            normalSqlField.setFieldTableAliasName(column.toString());
            if(ObjectUtils.isEmpty(selectExpressionItem.getAlias())){
                normalSqlField.setAlias(column.getColumnName());
            }else{
                normalSqlField.setAlias(selectExpressionItem.getAlias().getName());
            }
            if(ObjectUtils.isNotEmpty(column.getTable())){
                normalSqlField.setTable(tableAlias.get(column.getTable().getName()));
            }else {
                normalSqlField.setTable(tableNameList.get(0));
            }
            fieldList.add(normalSqlField);
        }

        fieldList.forEach(normalSqlField -> {

            if(StringUtils.isNotEmpty(joinFields.get(normalSqlField.getFieldTableAliasName()))){

                NormalSqlField parentNormalSqlField = fieldList.stream().filter( nsf ->
                    nsf.getFieldTableAliasName().equals(joinFields.get(normalSqlField.getFieldTableAliasName()))
                ).findFirst().get();

                normalSqlField.setParentTableName(parentNormalSqlField.getTable());
                normalSqlField.setParentFieldName(parentNormalSqlField.getFieldName());
            }

        });

        normalSqlStructure.setFieldList(fieldList);

        return normalSqlStructure;
    }

    /**
     * 解析join字段
     *
     * @param join
     * @param joinFields
     * @param tableName
     */
    public static void getJoinField(Join join, Map<String, String> joinFields, String tableName) {
        String joinFieldName = tableName + ".";

        LinkedList<Expression> expressions = (LinkedList) join.getOnExpressions();

        for (Expression expression : expressions) {
            EqualsTo equalsTo = (EqualsTo) expression;
            if (equalsTo.getRightExpression().toString().contains(joinFieldName)) {
                joinFields.put(equalsTo.getRightExpression().toString(), equalsTo.getLeftExpression().toString());
            } else if (equalsTo.getLeftExpression().toString().contains(joinFieldName)) {
                joinFields.put(equalsTo.getLeftExpression().toString(), equalsTo.getRightExpression().toString());
            }
        }
    }

    public static void main(String[] args) throws JSQLParserException {

//        getStructure("SELECT u.username as name, o.name as orderName, o.price as price, i.name as inventoryName" +
//            " FROM user AS u left join order as o on u.id = o.user_id left join inventory as i on i.id = o.inventory_id");

//        getStructure("SELECT u.id AS uId, o.user_id AS userId, o.inventory_id AS inventoryId," +
//            " i.id AS iId, u.username as name, o.name as orderName, o.price as price, i.*" +
//            " FROM user AS u left join order as o on u.id = o.user_id left join inventory as i on i.id = o.inventory_id");

//        getStructure("SELECT * FROM lc_test");

        getStructure("SELECT id, user_name AS username, age, address FROM lc_test_user");

    }

}
